package eypweb_bsc;

/*
 * To change this template, choose Tools | Templates and open the template in
 * the editor.
 */
import java.net.URL;
import java.sql.*;
import java.util.ArrayList;
import java.util.ResourceBundle;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.fxml.FXML;
import javafx.fxml.Initializable;
import javafx.geometry.Pos;
import javafx.scene.Group;
import javafx.scene.control.ChoiceBox;
import javafx.scene.control.ScrollPane;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TableView;
import javafx.scene.control.TextField;
import javafx.scene.control.cell.PropertyValueFactory;
import javafx.scene.input.MouseEvent;
import javafx.scene.layout.VBox;

/**
 *
 * @author Kohalmi Istvan
 */

// adatelemzeshez hasznalt kepernyot megvalosito osztaly
// fejlesztes alatt
public class QueryScreen implements Initializable {

    @FXML
    private ChoiceBox cbType;
    @FXML
    private TextField tfISIN;
    @FXML
    private Group isinGroup;
    @FXML
    private Group mainGroup;
    private ObservableList<TableColumn> columns = FXCollections.observableArrayList();

    @FXML
    private void handleSelectType(MouseEvent event) {
        Director director = Director.getInstance();
        AccessInfo ai = director.getAccessInfo();
        if (cbType.getSelectionModel().getSelectedItem() == null) {
            return;
        }
        String type = cbType.getSelectionModel().getSelectedItem().toString();

        if (type.equals("Teljes leltár")) {
            isinGroup.setDisable(true);
            ArrayList<String> ports = new ArrayList<String>();
            ArrayList<String> trsts = new ArrayList<String>();
            ArrayList<String> instr = new ArrayList<String>();
            ArrayList<String> isins = new ArrayList<String>();
            ObservableList<Inventory> invs = FXCollections.observableArrayList();
            ObservableList<Inventory> sumIsin = FXCollections.observableArrayList();
            ObservableList<Inventory> sumPort = FXCollections.observableArrayList();
            ObservableList<Inventory> sumTrst = FXCollections.observableArrayList();
            ObservableList<Inventory> sumInstr = FXCollections.observableArrayList();
            ArrayList<ObservableList<Inventory>> port_invs = new ArrayList<ObservableList<Inventory>>();
            ArrayList<ObservableList<Inventory>> trst_invs = new ArrayList<ObservableList<Inventory>>();
            ArrayList<ObservableList<Inventory>> instr_invs = new ArrayList<ObservableList<Inventory>>();
            ArrayList<ObservableList<Inventory>> isin_invs = new ArrayList<ObservableList<Inventory>>();
            try {
                Connection con = DriverManager.getConnection("jdbc:sqlserver://" + ai.getIPAddress() + ":" + ai.getPort() + ";databaseName=" + ai.getDBName(), ai.getUser(), ai.getPass());
                String leltarQuery = "SELECT DISTINCT portfolio.megnev AS p, vagyonkezelo.r_nev AS v, "
                        + "eszkoz_csoport.megnev AS e, isin.isin AS i, leltar.* FROM leltar, "
                        + "vagyonkezelo, isin, portfolio, isin_ecs, eszkoz_csoport "
                        + "WHERE leltar.id_vk = vagyonkezelo.id_vk "
                        + "AND leltar.id_isin = isin.id "
                        + "AND leltar.id_port = portfolio.id "
                        + "AND isin.id = isin_ecs.id_isin "
                        + "AND eszkoz_csoport.id = isin_ecs.id_ecs "
                        + "AND leltar.id IN "
                        + "(SELECT max(id) FROM leltar "
                        + "WHERE id_vetel NOT IN (SELECT DISTINCT id_vetel "
                        + "FROM leltar WHERE mennyiseg=0) "
                        + "GROUP BY id_vetel) ORDER BY p,v,e,i, leltar.id_vetel";
                PreparedStatement ps = con.prepareStatement(leltarQuery);
                ResultSet rs = ps.executeQuery();
                while (rs.next()) {
                    Inventory inv = new Inventory();
                    inv.setAcquisitionDate(rs.getDate(10).toString());
                    inv.setAcquisitionValue(rs.getLong(13));
                    inv.setDerecogniseCurrencyRateDelta(rs.getLong(18));
                    inv.setDerecogniseDividend(rs.getLong(16));
                    inv.setDerecogniseInterest(rs.getLong(15));
                    inv.setDerecogniseRateDelta(rs.getLong(17));
                    inv.setMarketValue(rs.getLong(14));
                    inv.setNominal(rs.getDouble(12));
                    inv.setPurchaseId(rs.getNString(6));
                    inv.setQuantity(rs.getInt(11));
                    inv.setAcquisitionValueCurrency(rs.getDouble(19));
                    inv.setAcquisitionRateCurrency(rs.getFloat(20));
                    inv.setSerial(rs.getNString(21));
                    String port = rs.getNString(1);
                    String trst = rs.getNString(2);
                    String inst = rs.getNString(3);
                    String isin = rs.getNString(4);
                    
                    if (!ports.contains(port)) {
                        ports.add(port);
                        ObservableList<Inventory> portInv = FXCollections.observableArrayList();
                        portInv.add(inv);
                        port_invs.add(portInv);
                    } else {
                        port_invs.get(ports.indexOf(port)).add(inv);
                    }
                    if (!trsts.contains(trst)) {
                        trsts.add(trst);
                        ObservableList<Inventory> trstInv = FXCollections.observableArrayList();
                        trstInv.add(inv);
                        trst_invs.add(trstInv);
                    } else {
                        trst_invs.get(trsts.indexOf(trst)).add(inv);
                    }
                    if (!instr.contains(inst)) {
                        instr.add(inst);
                        ObservableList<Inventory> instrInv = FXCollections.observableArrayList();
                        instrInv.add(inv);
                        instr_invs.add(instrInv);
                    } else {
                        instr_invs.get(instr.indexOf(inst)).add(inv);
                    }
                    if (!isins.contains(isin)) {
                        isins.add(isin);
                        ObservableList<Inventory> isinInv = FXCollections.observableArrayList();
                        isinInv.add(inv);
                        isin_invs.add(isinInv);
                    } else {
                        isin_invs.get(isins.indexOf(isin)).add(inv);
                    }
                    invs.add(inv);
                }
                for (ObservableList<Inventory> list : isin_invs) {
                    Inventory sumInv = new Inventory();
                    sumInv.setAcquisitionValue(new Long(0));
                    sumInv.setDerecogniseCurrencyRateDelta(new Long(0));
                    sumInv.setDerecogniseDividend(new Long(0));
                    sumInv.setDerecogniseInterest(new Long(0));
                    sumInv.setDerecogniseRateDelta(new Long(0));
                    sumInv.setMarketValue(new Long(0));
                    sumInv.setNominal(new Double(0));
                    sumInv.setQuantity(new Integer(0));
                    sumInv.setAcquisitionValueCurrency(new Double(0));
                    sumInv.setAcquisitionRateCurrency(new Float(0));
                    int i = 0;
                    for (Inventory inv : list) {
                        sumInv.setAcquisitionValue(sumInv.getAcquisitionValue() + inv.getAcquisitionValue());
                        sumInv.setDerecogniseCurrencyRateDelta(sumInv.getDerecogniseCurrencyRateDelta() + inv.getDerecogniseCurrencyRateDelta());
                        sumInv.setDerecogniseDividend(sumInv.getDerecogniseDividend() + inv.getDerecogniseDividend());
                        sumInv.setDerecogniseInterest(sumInv.getDerecogniseInterest() + inv.getDerecogniseInterest());
                        sumInv.setDerecogniseRateDelta(sumInv.getDerecognisedCurrencyRateDelta() + inv.getDerecognisedCurrencyRateDelta());
                        sumInv.setMarketValue(sumInv.getMarketValue() + inv.getMarketValue());
                        sumInv.setNominal(sumInv.getNominal() + inv.getNominal());
                        sumInv.setQuantity(sumInv.getQuantity() + inv.getQuantity());
                        sumInv.setAcquisitionValueCurrency(sumInv.getAcquisitionValueCurrency() + inv.getAcquisitionValueCurrency());
                        sumInv.setAcquisitionRateCurrency((sumInv.getAcquisitionRateCurrency() + inv.getAcquisitionRateCurrency()) * (i == 0 ? 1 : i) / (i + 1));
                    }
                    sumIsin.add(sumInv);
                }
                for (ObservableList<Inventory> list : trst_invs) {
                    Inventory sumInv = new Inventory();
                    sumInv.setAcquisitionValue(new Long(0));
                    sumInv.setDerecogniseCurrencyRateDelta(new Long(0));
                    sumInv.setDerecogniseDividend(new Long(0));
                    sumInv.setDerecogniseInterest(new Long(0));
                    sumInv.setDerecogniseRateDelta(new Long(0));
                    sumInv.setMarketValue(new Long(0));
                    sumInv.setNominal(new Double(0));
                    sumInv.setQuantity(new Integer(0));
                    sumInv.setAcquisitionValueCurrency(new Double(0));
                    sumInv.setAcquisitionRateCurrency(new Float(0));
                    int i = 0;
                    for (Inventory inv : list) {
                        sumInv.setAcquisitionValue(sumInv.getAcquisitionValue() + inv.getAcquisitionValue());
                        sumInv.setDerecogniseCurrencyRateDelta(sumInv.getDerecogniseCurrencyRateDelta() + inv.getDerecogniseCurrencyRateDelta());
                        sumInv.setDerecogniseDividend(sumInv.getDerecogniseDividend() + inv.getDerecogniseDividend());
                        sumInv.setDerecogniseInterest(sumInv.getDerecogniseInterest() + inv.getDerecogniseInterest());
                        sumInv.setDerecogniseRateDelta(sumInv.getDerecognisedCurrencyRateDelta() + inv.getDerecognisedCurrencyRateDelta());
                        sumInv.setMarketValue(sumInv.getMarketValue() + inv.getMarketValue());
                        sumInv.setNominal(sumInv.getNominal() + inv.getNominal());
                        sumInv.setQuantity(sumInv.getQuantity() + inv.getQuantity());
                        sumInv.setAcquisitionValueCurrency(sumInv.getAcquisitionValueCurrency() + inv.getAcquisitionValueCurrency());
                        sumInv.setAcquisitionRateCurrency((sumInv.getAcquisitionRateCurrency() + inv.getAcquisitionRateCurrency()) * (i == 0 ? 1 : i) / (i + 1));
                    }
                    sumTrst.add(sumInv);
                }
                for (ObservableList<Inventory> list : instr_invs) {
                    Inventory sumInv = new Inventory();
                    sumInv.setAcquisitionValue(new Long(0));
                    sumInv.setDerecogniseCurrencyRateDelta(new Long(0));
                    sumInv.setDerecogniseDividend(new Long(0));
                    sumInv.setDerecogniseInterest(new Long(0));
                    sumInv.setDerecogniseRateDelta(new Long(0));
                    sumInv.setMarketValue(new Long(0));
                    sumInv.setNominal(new Double(0));
                    sumInv.setQuantity(new Integer(0));
                    sumInv.setAcquisitionValueCurrency(new Double(0));
                    sumInv.setAcquisitionRateCurrency(new Float(0));
                    int i = 0;
                    for (Inventory inv : list) {
                        sumInv.setAcquisitionValue(sumInv.getAcquisitionValue() + inv.getAcquisitionValue());
                        sumInv.setDerecogniseCurrencyRateDelta(sumInv.getDerecogniseCurrencyRateDelta() + inv.getDerecogniseCurrencyRateDelta());
                        sumInv.setDerecogniseDividend(sumInv.getDerecogniseDividend() + inv.getDerecogniseDividend());
                        sumInv.setDerecogniseInterest(sumInv.getDerecogniseInterest() + inv.getDerecogniseInterest());
                        sumInv.setDerecogniseRateDelta(sumInv.getDerecognisedCurrencyRateDelta() + inv.getDerecognisedCurrencyRateDelta());
                        sumInv.setMarketValue(sumInv.getMarketValue() + inv.getMarketValue());
                        sumInv.setNominal(sumInv.getNominal() + inv.getNominal());
                        sumInv.setQuantity(sumInv.getQuantity() + inv.getQuantity());
                        sumInv.setAcquisitionValueCurrency(sumInv.getAcquisitionValueCurrency() + inv.getAcquisitionValueCurrency());
                        sumInv.setAcquisitionRateCurrency((sumInv.getAcquisitionRateCurrency() + inv.getAcquisitionRateCurrency()) * (i == 0 ? 1 : i) / (i + 1));
                    }
                    sumInstr.add(sumInv);
                }
                for (ObservableList<Inventory> list : port_invs) {
                    Inventory sumInv = new Inventory();
                    sumInv.setAcquisitionValue(new Long(0));
                    sumInv.setDerecogniseCurrencyRateDelta(new Long(0));
                    sumInv.setDerecogniseDividend(new Long(0));
                    sumInv.setDerecogniseInterest(new Long(0));
                    sumInv.setDerecogniseRateDelta(new Long(0));
                    sumInv.setMarketValue(new Long(0));
                    sumInv.setNominal(new Double(0));
                    sumInv.setQuantity(new Integer(0));
                    sumInv.setAcquisitionValueCurrency(new Double(0));
                    sumInv.setAcquisitionRateCurrency(new Float(0));
                    int i = 0;
                    for (Inventory inv : list) {
                        sumInv.setAcquisitionValue(sumInv.getAcquisitionValue() + inv.getAcquisitionValue());
                        sumInv.setDerecogniseCurrencyRateDelta(sumInv.getDerecogniseCurrencyRateDelta() + inv.getDerecogniseCurrencyRateDelta());
                        sumInv.setDerecogniseDividend(sumInv.getDerecogniseDividend() + inv.getDerecogniseDividend());
                        sumInv.setDerecogniseInterest(sumInv.getDerecogniseInterest() + inv.getDerecogniseInterest());
                        sumInv.setDerecogniseRateDelta(sumInv.getDerecognisedCurrencyRateDelta() + inv.getDerecognisedCurrencyRateDelta());
                        sumInv.setMarketValue(sumInv.getMarketValue() + inv.getMarketValue());
                        sumInv.setNominal(sumInv.getNominal() + inv.getNominal());
                        sumInv.setQuantity(sumInv.getQuantity() + inv.getQuantity());
                        sumInv.setAcquisitionValueCurrency(sumInv.getAcquisitionValueCurrency() + inv.getAcquisitionValueCurrency());
                        sumInv.setAcquisitionRateCurrency((sumInv.getAcquisitionRateCurrency() + inv.getAcquisitionRateCurrency()) * (i == 0 ? 1 : i) / (i + 1));
                    }
                    sumPort.add(sumInv);
                }
                ScrollPane sp = new ScrollPane();
                VBox vbox = new VBox();

                TableView tblInv = new TableView<Inventory>();
                tblInv.getColumns().setAll(columns);
                tblInv.getItems().setAll(invs);

                TableView tblPort = new TableView<Inventory>();
                tblPort.getColumns().setAll(columns);
                tblPort.getItems().setAll(sumPort);

                TableView tblTrst = new TableView<Inventory>();
                tblTrst.getColumns().setAll(columns);
                tblTrst.getItems().setAll(sumTrst);

                TableView tblInstr = new TableView<Inventory>();
                tblInstr.getColumns().setAll(columns);
                tblInstr.getItems().setAll(sumInstr);

                TableView tblIsin = new TableView<Inventory>();
                tblIsin.getColumns().setAll(columns);
                tblIsin.getItems().setAll(sumIsin);
                
                vbox.getChildren().addAll(tblInv, tblPort, tblTrst, tblInstr, tblIsin);
                vbox.setAlignment(Pos.CENTER);
                vbox.setSpacing(20);
                sp.setContent(vbox);
                sp.setPrefViewportHeight(500);
                sp.setPrefViewportWidth(700);
                sp.setFitToWidth(true);
                mainGroup.getChildren().clear();
                mainGroup.getChildren().add(sp);

                con.close();

            } catch (SQLException sqle) {
                sqle.printStackTrace();
            }
        } else if (cbType.getSelectionModel().getSelectedItem().toString().equals("Értékpapír követés")){
            isinGroup.setDisable(false);
        }
    }

    @Override
    public void initialize(URL url, ResourceBundle rb) {
        cbType.getItems().addAll("Teljes leltár", "Értékpapír követés");

        TableColumn<Inventory, String> col2 = new TableColumn<Inventory, String>("Vételi ID");
        col2.setCellValueFactory(new PropertyValueFactory("purchaseId"));

        TableColumn<Inventory, String> col3 = new TableColumn<Inventory, String>("Beszerzés dátuma");
        col3.setCellValueFactory(new PropertyValueFactory("acquisitionDate"));

        TableColumn<Inventory, Integer> col4 = new TableColumn<Inventory, Integer>("Mennyiség");
        col4.setCellValueFactory(new PropertyValueFactory("quantity"));

        TableColumn<Inventory, Double> col5 = new TableColumn<Inventory, Double>("Névérték");
        col5.setCellValueFactory(new PropertyValueFactory("nominal"));

        TableColumn<Inventory, Long> col6 = new TableColumn<Inventory, Long>("Beszerzési érték");
        col6.setCellValueFactory(new PropertyValueFactory("acquisitionValue"));

        TableColumn<Inventory, Long> col7 = new TableColumn<Inventory, Long>("Piaci érték");
        col7.setCellValueFactory(new PropertyValueFactory("marketValue"));

        TableColumn<Inventory, Long> col8 = new TableColumn<Inventory, Long>("ÉK - kamat");
        col8.setCellValueFactory(new PropertyValueFactory("derecogniseInterest"));

        TableColumn<Inventory, Long> col9 = new TableColumn<Inventory, Long>("ÉK - osztalék");
        col9.setCellValueFactory(new PropertyValueFactory("derecogniseDividend"));

        TableColumn<Inventory, Long> col10 = new TableColumn<Inventory, Long>("ÉK - egyéb piaci");
        col10.setCellValueFactory(new PropertyValueFactory("derecogniseRateDelta"));

        TableColumn<Inventory, Long> col11 = new TableColumn<Inventory, Long>("ÉK - deviza árfolyam-különbözet");
        col11.setCellValueFactory(new PropertyValueFactory("derecogniseCurrencyRateDelta"));

        columns.addAll(col2, col3, col4, col5, col6, col7, col8, col9, col10, col11);
    }
}
